package com.example.crm.dao;

import com.example.crm.domain.Connect;
import com.example.crm.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ConnectDAO {
    public Connect findById(int id) throws SQLException {
        // 1. 获取连接
        Connection conn = JDBCUtils.getConnection();

        // 2. 创建Statment
        String sql = "select * from connect where id=?";// (需要修改)
        PreparedStatement stmt = conn.prepareStatement(sql);

        stmt.setInt(1, id);

        // 3. 执行层语句
        ResultSet rs = stmt.executeQuery();
        Connect c = null;
        if (rs.next()) {
            String name = rs.getString("name");
            String tel = rs.getString("tel");

            c = new Connect(id, name, tel);

        }
        // 4. 释放资源
        JDBCUtils.destory(conn, stmt, null);

        return c;
    }




        public int insert(Connect c) throws SQLException {
       // 1. 获取连接
        Connection conn = JDBCUtils.getConnection();

        // 2. 创建Statment
        String sql = "insert into connect values (null,?,?)";
        PreparedStatement stmt = conn.prepareStatement(sql);

        // (需要修改)
        stmt.setString(1, c.getName());
        stmt.setString(2, c.getTel());

        // 3. 执行层语句
        int result = stmt.executeUpdate();

        // 4. 释放资源
        JDBCUtils.destory(conn, stmt, null);

        return result;
    }

    // 查询所有
    public List<Connect> findAll() throws SQLException
    {
        // 1. 获取连接
        Connection conn = JDBCUtils.getConnection();

        // 2. 创建Statment
        String sql = "select * from connect ";// (需要修改)
        PreparedStatement stmt = conn.prepareStatement(sql);

        // 3. 执行层语句
        ResultSet rs = stmt.executeQuery();
        List<Connect> list = new ArrayList<>();
        while(rs.next())
        {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            String tel= rs.getString("tel");

            Connect c = new Connect(id,name,tel);
            list.add(c);
        }

        // 4. 释放资源
        JDBCUtils.destory(conn, stmt, null);

        return list;
    }

    public int deleteById(int id) throws SQLException {
        // 1. 获取连接
        Connection conn = JDBCUtils.getConnection();

        // 2. 创建Statment
        String sql = "delete from connect where id=?";// (需要修改)
        PreparedStatement stmt = conn.prepareStatement(sql);

        // (需要修改)
        stmt.setInt(1, id);

        // 3. 执行层语句
        int result = stmt.executeUpdate();

        // 4. 释放资源
        JDBCUtils.destory(conn, stmt, null);

        return result;
    }


    public int update(Connect c) throws SQLException {
        // 1. 获取连接
        Connection conn = JDBCUtils.getConnection();

        // 2. 创建Statment
        String sql = "update connect set "
                + "name=?,"
                + "tel=?"
                +"where id=?";// (需要修改)
        PreparedStatement stmt = conn.prepareStatement(sql);
        System.out.println("-------------");
        System.out.println(c.getId());
        // (需要修改)
        stmt.setString(1, c.getName());
        stmt.setString(2, c.getTel());
        stmt.setInt(3, c.getId());


        // 3. 执行层语句
        int result = stmt.executeUpdate();

        // 4. 释放资源
        JDBCUtils.destory(conn, stmt, null);

        return result;
    }

}
